package fu.capstone.hanoicityguide.dataaccess.tourprovider;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import fu.capstone.hanoicityguide.R;
import fu.capstone.hanoicityguide.business.model.Tour;
import fu.capstone.hanoicityguide.business.model.TourProvider;
import fu.capstone.hanoicityguide.dataaccess.base.BaseDAOImpl;
import fu.capstone.hanoicityguide.dataaccess.base.DatabaseHelper;

public class TourProviderDAOImp extends BaseDAOImpl implements TourProviderDAO {

	private SQLiteDatabase database;
	private DatabaseHelper dbHelper;
	private Context context;

	public TourProviderDAOImp(Context ctx) {
		super(ctx);
		this.context = ctx;
	}

	@Override
	public ArrayList<TourProvider> listTourProvider(String sorter) {
		// get list TourProvider with sorter
		Cursor cursor = null;
		ArrayList<TourProvider> arrResult = new ArrayList<TourProvider>();
		try {

			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryTP = "SELECT TourProvider.[Id], TourProvider.[Name], TourProvider.[IconPath], TourProvider.[Website] "
					+ " FROM TourProvider ";
			// sort
			if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[2])) {
				queryTP = queryTP + "ORDER BY TourProvider.[Name] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[3])) {
				queryTP = queryTP + "ORDER BY TourProvider.[Name] ASC";
			}
			cursor = database.rawQuery(queryTP, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						TourProvider tourProvider = new TourProvider();
						tourProvider.setId(cursor.getInt(cursor
								.getColumnIndex("Id")));
						tourProvider.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						tourProvider.setIconPath(cursor.getString(cursor
								.getColumnIndex("IconPath")));
						tourProvider.setWebsite(cursor.getString(cursor
								.getColumnIndex("Website")));
						arrResult.add(tourProvider);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.v("Can't query DB listTourProvider", e.toString());
		} finally {
			closeDB(database, dbHelper);
		}
		return arrResult;
	}

	@Override
	public ArrayList<TourProvider> searchTourProvider(String keyword,
			String sorter) {
		// search list TourProvider with keyword and sorter
		Cursor cursor = null;
		ArrayList<TourProvider> arrResult = new ArrayList<TourProvider>();
		try {

			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryTP = "SELECT TourProvider.[Id], TourProvider.[Name], TourProvider.[IconPath], TourProvider.[Website] "
					+ " FROM TourProvider ";

			// filter by keyword
			queryTP = queryTP + "WHERE TourProvider.[Name] LIKE '%" + keyword
					+ "%' ";

			// sort
			if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[2])) {
				queryTP = queryTP + "ORDER BY TourProvider.[Name] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[3])) {
				queryTP = queryTP + "ORDER BY TourProvider.[Name] ASC";
			}

			cursor = database.rawQuery(queryTP, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						TourProvider tourProvider = new TourProvider();
						tourProvider.setId(cursor.getInt(cursor
								.getColumnIndex("Id")));
						tourProvider.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						tourProvider.setIconPath(cursor.getString(cursor
								.getColumnIndex("IconPath")));
						tourProvider.setWebsite(cursor.getString(cursor
								.getColumnIndex("Website")));
						arrResult.add(tourProvider);
					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.v("Can't query DB listTourProvider", e.toString());
		} finally {
			closeDB(database, dbHelper);
		}
		return arrResult;
	}

	@Override
	public ArrayList<Tour> browseTourProvider(int tourProviderId,
			String keyword, String sorter) {
		// TODO Auto-generated method stub

		Cursor cursor = null;
		ArrayList<Tour> arrTour = new ArrayList<Tour>();
		Tour tmpTour = null;

		try {
			dbHelper = connectDB();
			database = dbHelper.getReadableDatabase();
			String queryListTour = "SELECT Tour.[Id], Tour.[Name], "
					+ "AlbumImage.[IconPath], Rating.[RatingValue] , Tour.[Duration] FROM Tour "
					+ "LEFT JOIN AlbumImage ON Tour.[AlbumImageId] = AlbumImage.[Id] "
					+ "LEFT JOIN Rating ON Tour.[RatingId] = Rating.[Id] "
					+ "WHERE Tour.[ProviderId] == " + tourProviderId;

			// filter by keyword
			queryListTour = queryListTour + " AND Tour.[Name] LIKE '%"
					+ keyword + "%' ";

			// sort
			if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[0])) {
				queryListTour = queryListTour
						+ "ORDER BY Rating.[RatingValue] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[1])) {
				queryListTour = queryListTour
						+ "ORDER BY Rating.[RatingValue] ASC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[2])) {
				queryListTour = queryListTour + "ORDER BY Tour.[Name] DESC";
			} else if (sorter.equals(context.getResources().getStringArray(
					R.array.sorter_items)[3])) {
				queryListTour = queryListTour + "ORDER BY Tour.[Name] ASC";
			}

			cursor = database.rawQuery(queryListTour, null);
			if (cursor != null) {
				if (cursor.moveToFirst()) {
					do {
						tmpTour = new Tour();
						tmpTour.setId(cursor.getInt(cursor.getColumnIndex("Id")));
						tmpTour.setName(cursor.getString(cursor
								.getColumnIndex("Name")));
						tmpTour.setIconPath(cursor.getString(cursor
								.getColumnIndex("IconPath")));
						tmpTour.setRatingValue(Integer.parseInt(cursor
								.getString(cursor.getColumnIndex("RatingValue"))));
						tmpTour.setDuration(Float.parseFloat(cursor
								.getString(cursor.getColumnIndex("Duration"))));

						arrTour.add(tmpTour);

					} while (cursor.moveToNext());
				}
			}
		} catch (Exception e) {
			Log.v("Can't query DB browseTourProvider : ", e.toString());
		} finally {
			closeDB(database, dbHelper);
		}
		return arrTour;
	}

}
